/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package utilities;

import GUI.MainFunction;
import connection.ManLabConnection;
import entities.BillingDetails;
import entities.BillingTestDetail;
import entities.PatientDetails;
import entities.Test;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Dell
 */
public class BillManage {

    private ArrayList<BillingDetails> billList, billListSearch;
    private ArrayList<Test> testList;
    private ArrayList<BillingTestDetail> billTestList;
    private ArrayList<PatientDetails> patientList = new ArrayList<PatientDetails>();
    private SimpleDateFormatter sdf = new SimpleDateFormatter();
    private ManLabConnection cnn = new ManLabConnection();
    private ResultSet rs, rs1, rs2, rs3;
    private String qry, qry1, qry2;
    private MainFunction mf;

    public BillManage(ArrayList<BillingDetails> billList) {
        this.billList = new ArrayList<BillingDetails>();
        this.billList = billList;
    }

    public BillManage() {
    }

    public BillManage(ArrayList<BillingDetails> billList, ArrayList<Test> testList, MainFunction mf) {
        this.billList = new ArrayList<BillingDetails>();
        this.billList = billList;
        this.testList = testList;

        cnn = new ManLabConnection();
        this.mf = mf;

    }

    public boolean createNewBill(BillingDetails bd) {


        boolean cn = cnn.createConnection();


        qry1 = "insert into Billing(bDate,bAmount,pID) values('" + sdf.convertDateToString(bd.getbDate()) + "'," + bd.getbAmount() + "," + bd.getPatientDetails().getpID() + ")";

        boolean pass1 = cnn.executeQuery(qry1);


        qry = "select top 1 * from Billing order by bID desc";
        rs = cnn.createResultSet(qry);
        int bid = 0;
        boolean exc2 = false;
        try {
            while (rs.next()) {
                bid = rs.getInt("bID");
                bd.setbID(bid);

            }
        } catch (SQLException ex) {
            Logger.getLogger(BillManage.class.getName()).log(Level.SEVERE, null, ex);
        }



        int i = bd.getBillingTestDetail().size();


        boolean pass = false;
        if (i > 0) {

            for (int j = 0; j < i; j++) {
                qry2 = "insert into BillDetail values(" + bid + "," + bd.getBillingTestDetail().get(j).getTest().getTestID() + "," + bd.getBillingTestDetail().get(j).getTestCost() + ")";
                pass = cnn.executeQuery(qry2);
            }

        }

        cnn.closeConnection();
        if (pass == true && pass1 == true) {
            this.billList.add(bd);
            mf.reviewModel();
            ReportManage rm = new ReportManage(mf.getReportList(),mf);
            boolean suc = rm.createNewReportFromBill(bd);
            if (suc) {
                JOptionPane.showMessageDialog(mf, "Register Succesfully!!");
                return true;
            }
            return false;
        } else {
            JOptionPane.showMessageDialog(mf, "ERROR!!!!");
            return false;
        }
    }

    public boolean updateBill(BillingDetails bd) {
        boolean cn = cnn.createConnection();


        qry = "update Billing set bDate='" + sdf.convertDateToString(bd.getbDate()) + "',bAmount=" + bd.getbAmount() + ",pID=" + bd.getPatientDetails().getpID() + " where bID=" + bd.getbID();

        boolean pass1 = cnn.executeQuery(qry);

        qry1 = "delete from BillDetail where bID=" + bd.getbID();
        boolean pass2 = cnn.executeQuery(qry1);

        int i = bd.getBillingTestDetail().size();


        int bid = 0;
        bid = bd.getbID();

        boolean pass = false;
        if (i > 0) {

            for (int j = 0; j < i; j++) {
                qry2 = "insert into BillDetail values(" + bid + "," + bd.getBillingTestDetail().get(j).getTest().getTestID() + "," + bd.getBillingTestDetail().get(j).getTestCost() + ")";
                pass = cnn.executeQuery(qry2);
            }

        }

        cnn.closeConnection();
        if (pass1 == true) {

            mf.reviewModel();
            JOptionPane.showMessageDialog(mf, "Update Succesfully!!");
            return true;
        } else {
            JOptionPane.showMessageDialog(mf, "ERROR!!!!");
            return false;
        }
    }

    public boolean deleteBilling(BillingDetails bd) {


        boolean cn = cnn.createConnection();

        qry = "delete from BillDetail where bID=" + bd.getbID();
        boolean pass = cnn.executeQuery(qry);

        qry = "delete from Billing where bID=" + bd.getbID();
        boolean pass1 = cnn.executeQuery(qry);

        cnn.closeConnection();
        if (pass == true && pass1 == true) {

            JOptionPane.showMessageDialog(mf, "Delete Succesfully!!");
            return true;
        } else {
            JOptionPane.showMessageDialog(mf, "ERROR!!!!");
            return false;
        }

    }

    public ArrayList<BillingDetails> searchBill(Date startDate, Date endDate, ArrayList<BillingDetails> billList) {
        boolean cn = cnn.createConnection();

        qry = "select * from Billing where bDate between '" + sdf.convertDateToString(startDate) + "' and '" + sdf.convertDateToString(endDate) + "'";
        rs = cnn.createResultSet(qry);
        billListSearch = new ArrayList<BillingDetails>();
        try {
            while (rs.next()) {
                BillingDetails billingDetails = new BillingDetails();
                billingDetails.setbID(rs.getInt("bID"));
                qry = "select * from PatientDetail where pID=" + rs.getInt("pID");
                rs1 = cnn.createResultSet(qry);
                PatientDetails pd = new PatientDetails();
                try {
                    while (rs1.next()) {

                        pd.setpID(rs1.getInt("pID"));
                        pd.setpAddress(rs1.getString("pAddress"));
                        pd.setpDOB(rs1.getDate("pDOB"));
                        pd.setpFName(rs1.getString("pFName"));

                        pd.setpGender(rs1.getBoolean("pGender"));
                        pd.setpLName(rs1.getString("pLName"));
                        pd.setpPhone(rs1.getString("pPhone"));

                    }
                } catch (SQLException ex) {
                    Logger.getLogger(BillManage.class.getName()).log(Level.SEVERE, null, ex);
                }


                billingDetails.setPatientDetails(pd);

                qry = "select * from BillDetail where bID=" + rs.getInt("bID");
                rs2 = cnn.createResultSet(qry);
                billTestList = new ArrayList<BillingTestDetail>();
                try {
                    while (rs2.next()) {

                        BillingTestDetail billingTestDetail = new BillingTestDetail();
                        billingTestDetail.setTestCost(rs2.getFloat("testCost"));

                        qry = "select * from Test where testID=" + rs2.getInt("testID");
                        rs3 = cnn.createResultSet(qry);

                        try {
                            while (rs3.next()) {
                                Test t = new Test();
                                t.setTestID(rs3.getInt("testID"));
                                t.setTestCost(rs3.getFloat("testCost"));
                                t.setTestName(rs3.getString("testName"));

                                billingTestDetail.setTest(t);

                            }


                        } catch (SQLException ex) {
                            Logger.getLogger(BillManage.class.getName()).log(Level.SEVERE, null, ex);
                        }


                        billTestList.add(billingTestDetail);

                    }
                } catch (SQLException ex) {
                    Logger.getLogger(BillManage.class.getName()).log(Level.SEVERE, null, ex);
                }


                billingDetails.setBillingTestDetail(billTestList);
                billingDetails.setbAmount(rs.getFloat("bAmount"));
                billingDetails.setbDate(rs.getDate("bDate"));
                billListSearch.add(billingDetails);

            }
        } catch (SQLException ex) {
            Logger.getLogger(BillManage.class.getName()).log(Level.SEVERE, null, ex);
        }



        return billListSearch;
    }
}
